import { Service } from 'egg';
import * as blue from 'bluebird';
import * as _ from 'lodash';
import * as Excel from 'exceljs';

const DB_ACCOUNT_PRODUCT = 'platform_account_product';
// const DB_ACCOUNT = 'platform_account';
const DB_SMS = '_sms';

let reportCon = {};
let reportConTemp = {};

let reportHour = {};
let reportMonth = {};

let time1;

export default class SjService extends Service {
  
  /**
   * 2020年09月24日20:43:03 审计需求
   */
  public async sjsms() {
    time1 = new Date().getTime();
    reportCon = {};
    reportHour = {};
    reportMonth = {};
    const body = this.ctx.request.query;
    const {startTime, endTime} = body;

    const query = {
      submitTime: {
        $gte: startTime || '2017-01-01 00:00:00',
        $lte: endTime || '2020-09-01 00:00:00',
      },
      // m7Status: 'send',
    };
    this.app.logger.info('query: ', query);

    const accounts = await this.getAccounts();
    const all = accounts.length;

    let current = 0;
    await blue.map(accounts, async(accountDoc) => {
      const accountId = accountDoc.account;
      const datadb = accountDoc.dataDB;

      reportConTemp = {};

      const oneAll = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_SMS}`).count(query);
      if (oneAll === 0) {
        current++;
        this.printRate(all, current);
        return;
      }
      let oneCurrent = 0;

      const cursor = await this.app.commonDB.db(datadb).collection(`${accountId}${DB_SMS}`).find(query, {fields: {
        submitTime: 1,
        num: 1,
        status: 1,
        m7Status: 1,
      }});

      let doc: { submitTime: string, num: string, status: string, m7Status: string, };
      while (await cursor.hasNext()) {
        doc = await cursor.next();
        this.ctx.logger.info(doc.status, doc.m7Status);
        if (!doc.m7Status) {
          this.ctx.logger.info(doc.status, doc.m7Status);
        }
        this.processOneSms(doc);
        oneCurrent++;
        this.printOneRate(oneAll, oneCurrent);
      }
      this.processOneAccountEnd();
      current++;
      this.printRate(all, current);

    }, {concurrency: 1});

    await this.processAllEnd();
    this.ctx.sendSuccess();
  }

  public async printRate(all: number, current: number) {
    if (current % 100 === 0) {
      this.app.logger.info('=====================================================================');
      this.app.logger.info('======account==rate======' + `${all} ${current} ${Math.round(current/all*100)}%`);
    }
  }

  public async printOneRate(all: number, current: number) {
    if (current % 1000 === 0) {
      this.app.logger.info('======one====rate========' + `${all} ${current} ${Math.round(current/all*100)}%`);
    }
  }

  public async processAllEnd() {
    const filename = `./logs/sj-sms-${Date.now()}.xlsx`;
    const options = {
      filename,
      useStyles: true,
      useSharedStrings: true
    };
    const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
    // const workbook = new Excel.Workbook(options);

    // sheet1
    const sheet = workbook.addWorksheet('单号集中度');
    sheet.columns = [
      { key: 'month', header: '月份' } as any,
      { key: 'count', header: '单手机号接收短信数量' } as any,
      { key: 'phone_count', header: '手机号数量' } as any,
    ];
    let months = Object.keys(reportCon);
    months = months.sort((a, b) => {
      return a > b ? 1: -1
    });
    months.forEach((month) => {
      const one = reportCon[month];
      let list = Object.keys(one);
      list = list.sort((a, b) => {
        return parseInt(a) < parseInt(b) ? 1: -1
      });
      list.forEach((count) => {
        const obj = {
          month,
          count,
          phone_count: one[count],
        };
        sheet.addRow(obj).commit();;
      })
    });
    sheet.commit();

    // sheet2
    const sheet2 = workbook.addWorksheet('短信发送时间分布');
    sheet2.columns = [
      { key: 'time', header: '时间段' } as any,
      { key: 'count', header: '短信发送量' } as any,
    ];
    let houres = Object.keys(reportHour);
    houres = houres.sort((a, b) => {
      return a > b ? 1: -1
    });
    houres.forEach((hour) => {
      sheet2.addRow({
        time: hour,
        count: reportHour[hour],
      }).commit();
    });
    sheet2.commit();

    // sheet3
    const sheet3 = workbook.addWorksheet('短信发送量趋势分析');
    sheet3.columns = [
      { key: 'time', header: '月份' } as any,
      { key: 'count', header: '短信发送量' } as any,
    ];
    let monthList = Object.keys(reportMonth);
    monthList = monthList.sort((a, b) => {
      return a > b ? 1: -1
    });
    monthList.forEach((month) => {
      sheet3.addRow({
        time: month,
        count: reportMonth[month],
      }).commit();
    });
    this.app.logger.info('生成表格成功')
    sheet3.commit();

    console.time('执行commit');
    await workbook.commit();
    console.timeEnd('执行commit');
    const time2 = new Date().getTime();
    this.ctx.logger.info(`导出耗时：${time2 - time1} ms`);
  }

  public processOneAccountEnd() {
    // 处理report1
    Object.keys(reportConTemp).forEach((month) => {
      const dis = reportConTemp[month];
      if (!reportCon[month]) {
        reportCon[month] = {};
      }
      const one = reportCon[month];

      Object.keys(dis).forEach(num => {
        const count = dis[num];
        if (!one[count]) {
          one[count] = 0;
        }
        one[count]++;
      });
    });
    reportConTemp = {};
    // other
  }

  public processOneSms(sms: { submitTime: string, num: string }) {
    const month = this.getMonthFromTime(sms.submitTime);
    const hour = this.getHourFromTime(sms.submitTime);
    this.reportConcentrate(month, sms.num);
    this.reportHour(hour);
    this.reportMonth(month);
  }

  // 1.DA1单手机号接受短信数量集中度分析
  public reportConcentrate(month: string, num: string) {
    if (!reportConTemp[month]) {
      reportConTemp[month] = {};
    }
    if (!reportConTemp[month][num]) {
      reportConTemp[month][num] = 0;
    }
    reportConTemp[month][num]++;
  }

  //2.DA2 短信发送时间分布分析
  public reportHour(hour: string) {
    if (!reportHour[hour]) {
      reportHour[hour] = 0;
    }
    reportHour[hour]++;
  }

  // 3.DA4 短信发送量趋势分析
  public reportMonth(month: string) {
    if (!reportMonth[month]) {
      reportMonth[month] = 0;
    }
    reportMonth[month]++;
  }

  // "2020-08-18 16:02:26" => "202008"
  public getMonthFromTime(time: string): string {
    return time.substr(0, 4) + time.substr(5, 2);
  }

  // "2020-08-18 16:02:26" => "16"
  public getHourFromTime(time: string): string {
    return time.substr(11,2);
  }

  public async findSms(account: string, datadb: string) {
    return this.app.commonDB.db(datadb).collection(`${account}_${DB_SMS}`).find({});
  }

  public async getAccounts(): Promise<{dataDB: string, account:string}[]> {
    const accounts = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
      .find({product: 'cc', createTime: {$lt: '2020-09-01 00:00:00'}}, {fields: {_id: 1, dataDB:1, account:1}}).toArray();
    this.ctx.logger.info(accounts.length);
    return accounts as any;
  }

  // public async getDataDB(account) {
  //   let db = dbCache[account];
  //   if (!db) {
  //     const doc = await this.app.commonDB.db('bill').collection(DB_ACCOUNT_PRODUCT)
  //       .findOne({_id: account + '_cc'}, {fields: {dataDB:1}});
  //     if (!doc) {
  //       return null;
  //     }
  //     dbCache[account] = doc.dataDB;
  //     db = doc.dataDB;
  //   }
  //   return db;
  // }

}
